package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Custom;
import cn.gson.crm.model.entity.Student;

public class StudentDao {
	// 新建一个学生信息
	public boolean insertOneStudent(Custom c, String classid, String password) throws SQLException {
		String sql = "INSERT INTO student (stu_id,stu_name," + "stu_gender,stu_address,stu_telphone,"
				+ "stu_degree_id,stu_qq,stu_parentTel," + "stu_parentname,stu_status_id,class_id,stu_password) "
				+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
		boolean result = Jdbc.getInstace().executeUpdate(sql, c.getCustomId(), c.getCustomName(), c.getCustomGender(),
				c.getCustomAddress(), c.getCustomTel(), c.getCustomDegreeId(), c.getCustomQQ(), c.getCustomParentTel(),
				c.getCustomParentName(), 4, classid, password);
		return result;

	}

	// 通过学生id获得一个学生的信息
	public Student getOneStudentById(String id) throws SQLException {
		String sql = "SELECT * FROM student WHERE stu_id=?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, id);
		Student student = null;
		if (data != null) {
			student = builderStudent(data);//
		}
		return student;
	}

	// 通过班级id获取到班级内的所有同学
	public List<Student> getAllClassMateByClassId(String id) throws SQLException {
		String sql = "SELECT * FROM student WHERE class_id = ? AND stu_status_id=4";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql, id);
		List<Student> studentList = new ArrayList<>();
		Student student = null;
		for (Map<String, Object> map : data) {
			student = builderStudent(map);
			studentList.add(student);
		}
		return studentList;

	}

	// 通过学生的id进行修改学生的状态
	public boolean updetaStudentToGraduate(int statue, String id) throws SQLException {
		String sql = "UPDATE `student` SET `stu_status_id`=? WHERE stu_id=?;";
		boolean result = Jdbc.getInstace().executeUpdate(sql, statue, id);
		return result;
	}

	/**
	 * 获得所有已毕业的学生的信息
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Student> getGraduateByStatueId() throws SQLException {
		String sql = "SELECT * FROM student WHERE stu_status_id=6";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql);
		List<Student> graduateStudentList = new ArrayList<>();
		Student student = null;
		for (Map<String, Object> map : data) {
			student = builderStudent(map);
			graduateStudentList.add(student);
		}
		return graduateStudentList;
	}

	private Student builderStudent(Map<String, Object> map) {
		Student student = new Student();
		student.setStuId((Integer) map.get("stu_id"));
		student.setStuName((String) map.get("stu_name"));
		student.setStuGender((String) map.get("stu_gender"));
		student.setStuAddress((String) map.get("stu_address"));
		student.setStuTel((String) map.get("stu_telphone"));
		student.setStuQQ((String) map.get("stu_qq"));
		student.setStuParentName((String) map.get("stu_parentname"));
		student.setStuParentTel((String) map.get("stu_parentTel"));
		student.setStuDegreeId((Integer) map.get("stu_degree_id"));
		student.setStuStatusId((Integer) map.get("stu_status_id"));
		student.setClassId((Integer) map.get("class_id"));
		return student;

	}
}
